Oracle的存储过程，是我们使用数据库应用开发的重要工具手段。在存储过程中，我们大部分应用场景都是使用DML语句进行数据增删改操作。本篇中，我们一起探讨一下数据定义语句DDL在存储过程中使用的细节和要点。

1、“借道而行”的DDL

从Oracle

PL/SQL和存储过程程序开发原则上，应该是不鼓励在SP中使用DDL语句的。首先一个表现，就是Oracle在编译时就不允许直接在SP中使用DDL语句。下面我们使用Oracle

10gR2作为实验环境。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.1.0 - Prod

PL/SQL Release10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version10.2.0.1.0 - Production

NLSRTL Version10.2.0.1.0 – Production

建立存储过程p_test_nc，进行简单的数据表创建。

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4 create

table t (id number);

5 end P_TEST_NC;

6 /

Warning: Procedure created with compilation errors

SQL> select name, text from user_errors;

NAME TEXT

----------

--------------------------------------------------------------------------------

P_TEST_NC PLS-00103:出现符号"CREATE"在需要下列之一时：

begin case declare exit

for goto if loop mod null pragma raise return select update

while with

close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe

显然，在编译时Oracle就报错不允许存储过程创建。之后的实验drop和truncate

table操作，也都是不允许直接在存储过程中书写DDL语句。说明起码使用直接的DDL语句，存储过程是不能编译通过的。

那么，有没有什么折中的方法呢？我们说是有的，就是借助“execute

immediate”方法，“绕过”编译过程中对DDL的屏蔽。我们使用truncate table DDL语句实验。

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4 execute

immediate'truncate table t';

5 end P_TEST_NC;

6 /

Procedure created

编译通过了，DDL语句以一个字符串的形式避开了编译时Oracle的语法检查，编译成功。那么，执行起来会不会报运行时错误呢？

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

执行成功，说明：在Oracle存储过程中，可以使用exectue

immediate语句绕开编译时对DDL语句的检查，生成运行代码。

2、SP中DDL权限

任何程序编译执行都会伴随着语法语义的一系列检查。使用execute

immediate虽然可以回避编译时检查，但是SQL语句还是面临着运行时检查的问题。下面看实验的例子。

--在scott用户下进行试验；

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4 execute

immediate 'create table t(id number)';

5 end P_TEST_NC;

6 /

Procedure created–编译时通过；

SQL> exec p_test_nc;

begin p_test_nc; end;

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

在用户自己的schema下创建数据表，难道是不允许的吗？显然不是。

SQL> create table m (id number);

Table created

单独创建是允许的，说明是由于权限机制导致的问题。我们切换到sys用户上，提高scott用户权限。

Connected as SYS

--赋予最高创建数据表的系统权限；

SQL> grant create any table to scott;

Grant succeeded

切换回scott用户，继续实验。

Connected to Oracle Database10gEnterpriseEdition

Release10.2.0.1.0

Connected as scott

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

SQL> select * from t;

ID

----------

执行成功！这个原因是什么呢？还是由于存储过程权限体系特点和DDL语句特点共同造成的。

在之前笔者的系列文章《所有者权限和调用者权限》(http://space.itpub.net/17203031/viewspace-692161)中，介绍了Oracle存储过程采用的两种权限体系方式和role权限在存储过程执行中的特殊性。

默认情况下，Oracle对存储过程是使用所有者权限，也就是说：如果用户B调用了用户A

schema下的一个存储过程，其中使用的对象权限和系统权限，全部都是用户A的。如果用户A没有权限，用户B执行要报错。

同时，用户的角色权限在进入存储过程后，会被剥离掉，是不其效果的。

结合上面的实验，就好解释了：scott自身只拥有一个resource的角色权限，单独在SQL中使用没有问题。进入到SP之后，这个create

table的权限就被剥离掉了。而该SP存在被其他用户调用生成数据表的可能。所以会在运行时报错权限不足。

当我们显示的赋予scott用户create any table/create

table之后，系统权限就可以渗透到SP中起效果了。

这并不是解决该问题的唯一方法。此处我们可以使用调用者权限机制，改写SP代码。首先我们剔除掉scott的create any

table权限。

Connected to Oracle Database10gEnterpriseEdition

Release10.2.0.1.0

Connected as SYS

SQL> revoke create any table from scott;

Revoke succeeded

Connected to Oracle Database10gEnterpriseEdition

Release10.2.0.1.0

Connected as scott

SQL> exec p_test_nc;

begin p_test_nc; end;

ORA-01031:权限不足

ORA-06512:在"SCOTT.P_TEST_NC", line 4

ORA-06512:在line 1

我们改写代码为：

SQL> create or replace procedure P_TEST_NC

2 Authid Current_User

3 is

4 begin

5 execute

immediate 'create table t (id number)';

6 end P_TEST_NC;

7 /

Procedure created

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

执行成功，这里使用“authid

Current_user”将存储过程转化为调用者权限。每次调用存储过程，都是动态根据调用者的权限构成去判定是否有权限，这样就回避了该问题的出现。

总之：在使用DDL在存储过程中时，权限管理和使用的复杂度是在增加。

4、DDL对事务的提交影响

将DDL语句放置在存储过程中，潜在最大风险就是对事务管理的破坏。在Oracle中，如果调用一个DDL语句，潜藏效果就是将当前会话的未提交事务进行提交。这个过程显然是对原有的事务逻辑破坏。

SQL> create table m (id number);

Table created

SQL> select * from m;

ID

----------

SQL> create or replace procedure P_TEST_NC

2 is

3 begin

4 insert

into m values (3);

5 execute

immediate 'truncate table t';

6

7 rollback;

8 end P_TEST_NC;

9 /

Procedure created

--执行代码

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

--事务提交

SQL> select * from m;

ID

----------

3

从上面的例子上，我们可以清楚的看到现象。由于中间的truncate

table操作，引起数据表m的插入操作被提交commit。而真正的事务逻辑可能是一个rollback。

所以，在SP中使用DDL命令，可能引起业务逻辑的不可控提交和数据不一致，这个风险在任何应用中是不可以允许的。

那么，有没有方法回避这个过程呢？经一个同事提醒，的确可以使用手段回避。

5、DDL与自治事务

自治事务(AUTONOMOUS_TRANSACTION)是保证在事务进行过程中一段独立的事务过程。如果在DDL操作外套入一个自治事务过程，是否就可以回避问题了。

SQL> select * from m;

ID

----------

SQL> create or replace procedure P_TEST_NC is

2 procedure p_inner_test

3 is

4 PRAGMA

AUTONOMOUS_TRANSACTION;

5 begin

6 --调用ddl

7 execute immediate 'truncate table t';

8 end;

9 begin

10 insert

into m values (3);

11 p_inner_test;

12

13 rollback;

14 end P_TEST_NC;

15 /

Procedure created

SQL> exec p_test_nc;

PL/SQL procedure successfully completed

\

SQL> select * from m;

ID

----------

实验成功，通过自治事务的确可以回避DDL的事务问题。

6、结论

DDL在SP中，与常规的DML操作差异很大。这种差异不仅仅是语法上，更多的是权限、事务等更深层次复杂的差异。所以，从Oracle的角度看，尽量少在SP中使用DDL语句，避免出现不可控的问题。

由于分区需要，使用了动态sql，原经理说存储过程是不能使用ddl语句的，由上可知，其实可以，但可能会遇权限问题，所以动态sql是最保险的方法